from threading import local

import pymysql.cursors
from pymysql import connect


# 事务管理装饰器
def transation(func):
    def inner(*args, **kwargs):
        conn = DBUtils.getConn()
        try:
            # 执行代码
            ret = func(*args, **kwargs)
            conn.commit()  # 提交
        except Exception as e:
            print(e)
            conn.rollback()
        finally:
            DBUtils.close()  # 关闭连接
        return ret

    return inner


class DBUtils:
    _thread_local = local()  # 线程池  类属型

    # 获取连接
    @classmethod
    def getConn(cls):
        # 如果线程池没有连接,就获取并存放
        if not hasattr(cls._thread_local, 'conn'):
            conn = connect(
                user='root',  # The first four arguments is based on DB-API 2.0 recommendation.
                password="123456",
                host='localhost',
                database='kgcnews',
                port=3306,  # 整型
                # autocommit=False  默认关闭自动提交
            )
            setattr(cls._thread_local, 'conn', conn)  # 放连接
        return getattr(cls._thread_local, 'conn')  # 获取连接返回

    # 插入一行
    @classmethod
    def insert(cls, sql, args=None):

        conn = cls.getConn()
        if not conn:
            return None
        with conn.cursor() as cursor:
            row = cursor.execute(sql, args)
            # 获取插入后的主键
            id = cursor.lastrowid
            return row, id

    # 批量插入
    @classmethod
    def batch(cls, sql, args=None):
        conn = cls.getConn()
        if not conn:
            return None
        with conn.cursor() as cursor:
            row = cursor.executemany(sql, args)
            return row

    # 删除
    @classmethod
    def delete(cls, sql, args=None):
        row, id = cls.insert(sql, args)
        return row

    # 修改
    @classmethod
    def update(cls, sql, args=None):
        row, id = cls.insert(sql, args)
        return row

    # 查询一行
    @classmethod
    def select_one(cls, sql, args=None):
        conn = cls.getConn()
        if not conn:
            return None

        # pymysql-02.cursors.DictCursor,自动将数据转换为字典
        with conn.cursor(pymysql.cursors.DictCursor) as cursor:
            row = cursor.execute(sql, args)  # 执行行数
            data = cursor.fetchone()  # fetchone返回一条数据
            # cols = [item[0] for item in cursor.description]
            # return dict(zip(cols, data))  # 压缩获取字典
            return data

    # 查询多条
    @classmethod
    def select_more(cls, sql, args=None):
        conn = cls.getConn()
        if not conn:
            return None
        with conn.cursor(pymysql.cursors.DictCursor) as cursor:
            row = cursor.execute(sql, args)  # 执行行数
            res = cursor.fetchall()  # fetchone返回一条数据
            # cols = [item[0] for item in cursor.description]
            # return [dict(zip(cols, obj)) for obj in res]
            return res

    # 关闭连接
    @classmethod
    def close(cls):
        conn = cls.getConn()
        if conn:
            conn.close()
            delattr(cls._thread_local, 'conn')
